package com.huge.simplepoi.core;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.huge.simplepoi.annotation.Excel;
import com.huge.simplepoi.constants.JavaType;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;


/**
 * @author jihu
 * date 2022-03-22
 */
public abstract class SimplePoi {

    public Workbook workbook;

    //标题样式
    protected CellStyle titleCellStyle;

    //标题字体
    protected Font titleFont;

    //字段意思样式
    protected CellStyle columnCellStyle;

    //字段字体
    protected Font columnFont;

    //普通单元格样式
    protected CellStyle cellStyle;

    //普通单元格字体
    protected Font font;

    public SimplePoi(Workbook workbook){
        this.workbook = workbook;
        initStyle();
    }

    /**
     * 初始化样式
     */
    public void initStyle(){
        this.titleCellStyle = workbook.createCellStyle();
        this.columnCellStyle = workbook.createCellStyle();
        this.cellStyle = workbook.createCellStyle();
        this.titleFont = workbook.createFont();
        this.columnFont = workbook.createFont();
        this.font = workbook.createFont();

        titleFont.setFontName("宋体");
        titleFont.setBold(true);//加粗
        titleFont.setFontHeightInPoints((short) 24);//设置字体大小
        titleCellStyle.setFont(titleFont);//设置字体
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        columnFont.setFontName("宋体");
        columnFont.setBold(true);//加粗
        columnFont.setFontHeightInPoints((short) 16);//设置字体大小
        columnCellStyle.setFont(columnFont);//设置字体
        columnCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        columnCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        columnCellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        columnCellStyle.setBorderRight(BorderStyle.THIN);//有边框
        columnCellStyle.setBorderTop(BorderStyle.THIN);//上边框
        columnCellStyle.setBorderBottom(BorderStyle.THIN);//下边框

        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        cellStyle.setFont(font);//设置字体
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//有边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
    }

    public Sheet getSheet(String sheetName){
        return workbook.getSheet(sheetName);
    }

    public Sheet getSheet(int sheetIndex){
        return workbook.getSheetAt(sheetIndex);
    }

    public Row getRow(int sheetIndex, int rowNum) {
        Sheet sheet = getSheet(sheetIndex);
        return getRow(sheet,rowNum);
    }

    public Row getRow(String sheetName, int rowNum) {
        Sheet sheet = getSheet(sheetName);
        return getRow(sheet,rowNum);
    }

    public Row getRow(Sheet sheet, int rowNum) {
        return sheet.getRow(rowNum) == null ? sheet.createRow(rowNum) : sheet.getRow(rowNum);
    }

    public Cell getCell(int sheetIndex, int rowNum, int cellNum){
        Sheet sheet = getSheet(sheetIndex);
        return getCell(sheet,rowNum,cellNum);
    }

    public Cell getCell(String sheetName, int rowNum, int cellNum){
        Sheet sheet = getSheet(sheetName);
        return getCell(sheet,rowNum,cellNum);
    }

    public Cell getCell(Sheet sheet, int rowNum, int cellNum){
        Row row = getRow(sheet, rowNum);
        return getCell(row,cellNum);
    }

    public Cell getCell(Row row, int cellNum){
        return row.getCell(cellNum) == null  ? row.createCell(cellNum) : row.getCell(cellNum);
    }

    public String getCellValue(int sheetIndex, int rowNum, int cellNum){
        Sheet sheet = getSheet(sheetIndex);
        return getCellValue(sheet,rowNum,cellNum);
    }

    public String getCellValue(String sheetName, int rowNum, int cellNum){
        Sheet sheet = getSheet(sheetName);
        return getCellValue(sheet,rowNum,cellNum);
    }

    public String getCellValue(Sheet sheet, int rowNum, int cellNum){
        Row row = getRow(sheet, rowNum);
        return getCellValue(row,cellNum);
    }

    public String getCellValue(Row row, int cellNum){
        Cell cell = getCell(row,cellNum);
        return getCellValue(cell);
    }

    public String getCellValue(Cell cell){
        if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else {
            return String.valueOf(cell.getStringCellValue());
        }
    }

    public void setCell(int sheetIndex, int rowNum, int cellNum, Integer value){
        Sheet sheet = getSheet(sheetIndex);
        setCell(sheet,rowNum,cellNum,value);
    }

    public void setCell(String sheetName, int rowNum, int cellNum, Integer value){
        Sheet sheet = getSheet(sheetName);
        setCell(sheet,rowNum,cellNum,value);
    }

    public void setCell(Sheet sheet, int rowNum, int cellNum, Integer value){
        Row row = getRow(sheet, rowNum);
        setCell(row,cellNum,value);
    }

    public void setCell(Row row, int cellNum, Integer value){
        Cell cell = getCell(row, cellNum);
        setCell(cell,value);
    }

    public void setCell(Cell cell, Integer value){
        value = value == null ? 0 : value;
        cell.setCellValue(value);
    }

    public void setCell(int sheetIndex, int rowNum, int cellNum, String value){
        Sheet sheet = getSheet(sheetIndex);
        setCell(sheet,rowNum,cellNum,value);
    }

    public void setCell(String sheetName, int rowNum, int cellNum, String value){
        Sheet sheet = getSheet(sheetName);
        setCell(sheet,rowNum,cellNum,value);
    }

    public void setCell(Sheet sheet, int rowNum, int cellNum, String value){
        Row row = getRow(sheet, rowNum);
        setCell(row,cellNum,value);
    }

    public void setCell(Row row, int cellNum, String value){
        Cell cell = getCell(row, cellNum);
        setCell(cell,value);
    }

    public void setCell(Cell cell, String value){
        value = value == null ? "/" : value;
        cell.setCellValue(value);
    }

    public void setCell(int sheetIndex, int rowNum, int cellNum, BigDecimal value){
        Sheet sheet = getSheet(sheetIndex);
        setCell(sheet,rowNum,cellNum,value);
    }

    public void setCell(String sheetName, int rowNum, int cellNum, BigDecimal value){
        Sheet sheet = getSheet(sheetName);
        setCell(sheet,rowNum,cellNum,value);
    }

    public void setCell(Sheet sheet, int rowNum, int cellNum, BigDecimal value){
        Row row = getRow(sheet, rowNum);
        setCell(row,cellNum,value);
    }

    public void setCell(Row row, int cellNum, BigDecimal value){
        Cell cell = getCell(row, cellNum);
        setCell(cell,value);
    }

    public void setCell(Cell cell, BigDecimal value){
        value = value == null ? BigDecimal.ZERO : value;
        cell.setCellValue(value.toString());
    }

    public void setCell(int sheetIndex, int rowNum, int cellNum, Integer value, CellStyle cellStyle){
        Sheet sheet = getSheet(sheetIndex);
        setCell(sheet,rowNum,cellNum,value,cellStyle);
    }

    public void setCell(String sheetName, int rowNum, int cellNum, Integer value, CellStyle cellStyle){
        Sheet sheet = getSheet(sheetName);
        setCell(sheet,rowNum,cellNum,value,cellStyle);
    }

    public void setCell(Sheet sheet, int rowNum, int cellNum, Integer value, CellStyle cellStyle){
        Row row = getRow(sheet, rowNum);
        setCell(row,cellNum,value,cellStyle);
    }

    public void setCell(Row row, int cellNum, Integer value, CellStyle cellStyle){
        Cell cell = getCell(row, cellNum);
        setCell(cell,value,cellStyle);
    }

    public void setCell(Cell cell, Integer value, CellStyle cellStyle){
        value = value == null ? 0 : value;
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle);
    }

    public void setCell(int sheetIndex, int rowNum, int cellNum, String value, CellStyle cellStyle){
        Sheet sheet = getSheet(sheetIndex);
        setCell(sheet,rowNum,cellNum,value,cellStyle);
    }

    public void setCell(String sheetName, int rowNum, int cellNum, String value, CellStyle cellStyle){
        Sheet sheet = getSheet(sheetName);
        setCell(sheet,rowNum,cellNum,value,cellStyle);
    }

    public void setCell(Sheet sheet, int rowNum, int cellNum, String value, CellStyle cellStyle){
        Row row = getRow(sheet, rowNum);
        setCell(row,cellNum,value,cellStyle);
    }

    public void setCell(Row row, int cellNum, String value, CellStyle cellStyle){
        Cell cell = getCell(row, cellNum);
        setCell(cell,value,cellStyle);
    }

    public void setCell(Cell cell, String value, CellStyle cellStyle){
        value = value == null ? "/" : value;
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle);
    }

    public void setCell(int sheetIndex, int rowNum, int cellNum, BigDecimal value, CellStyle cellStyle){
        Sheet sheet = getSheet(sheetIndex);
        setCell(sheet,rowNum,cellNum,value,cellStyle);
    }

    public void setCell(String sheetName, int rowNum, int cellNum, BigDecimal value, CellStyle cellStyle){
        Sheet sheet = getSheet(sheetName);
        setCell(sheet,rowNum,cellNum,value,cellStyle);
    }

    public void setCell(Sheet sheet, int rowNum, int cellNum, BigDecimal value, CellStyle cellStyle){
        Row row = getRow(sheet, rowNum);
        setCell(row,cellNum,value,cellStyle);
    }

    public void setCell(Row row, int cellNum, BigDecimal value, CellStyle cellStyle){
        Cell cell = getCell(row, cellNum);
        setCell(cell,value,cellStyle);
    }

    public void setCell(Cell cell, BigDecimal value, CellStyle cellStyle){
        value = value == null ? BigDecimal.ZERO : value;
        cell.setCellValue(value.toString());
        cell.setCellStyle(cellStyle);
    }

    public <T> SimplePoi export(String sheetName, List<T> datas, Class<T> clazz){
        Sheet sheet = workbook.createSheet(sheetName);
        return this.export(sheet,null,datas,clazz);
    }

    public <T> SimplePoi export(String sheetName, String title, List<T> datas, Class<T> clazz){
        Sheet sheet = workbook.createSheet(sheetName);
        return this.export(sheet,title,datas,clazz);
    }

    public <T> SimplePoi export(Sheet sheet, String title, List<T> datas, Class<T> clazz){
        List<Field> fields = this.getFiledInfo(clazz).stream().sorted((a,b) -> {
            Integer a1 = a.getAnnotation(Excel.class).order();
            Integer b1 = a.getAnnotation(Excel.class).order();
            return a1.compareTo(b1);
        }).collect(Collectors.toList());

        //小标题
        Integer smallTitleRow = 0;
        //数据开始行
        Integer dataStartRow = 1;

        if(StrUtil.isNotBlank(title)){
            smallTitleRow++;
            dataStartRow++;
            //标题行合并
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,fields.size()-1));
            //冻结首行和标题
            sheet.createFreezePane(0,2,0,2);
            setCell(sheet,0,0,title,this.titleCellStyle);
        }

        //小标题处理
        for (int i = 0; i < fields.size(); i++) {
            Excel excel = fields.get(i).getAnnotation(Excel.class);
            sheet.setColumnWidth(i,excel.width() * 256);
            setCell(sheet, smallTitleRow, i ,excel.name(),this.columnCellStyle);
        }

        //设置筛选
        CellRangeAddress cellRangeAddress = new CellRangeAddress(smallTitleRow,smallTitleRow,0,fields.size()-1);
        sheet.setAutoFilter(cellRangeAddress);

        try {
            for (T data : datas) {
                for (int i = 0; i < fields.size(); i++) {
                    Field field = fields.get(i);
                    field.setAccessible(true);
                    if(field.getType().getSimpleName().toLowerCase().equals(JavaType.DATE)){
                        setCell(sheet,dataStartRow,i, DateUtil.format((Date) field.get(data), fields.get(i).getAnnotation(Excel.class).dateFormat()),this.cellStyle);
                    }else {
                        setCell(sheet,dataStartRow,i,(String) field.get(data),this.cellStyle);
                    }
                    field.setAccessible(false);
                }
                dataStartRow++;
            }

            return this;
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e.getMessage());
        }

    }



    public <T> List<T> importExcel(int sheetIndex,Class<T> clazz,int columnNameRowNum,int dataStartRowNum) throws Exception {
        Sheet sheet = getSheet(sheetIndex);
        return this.importExcel(sheet,clazz,columnNameRowNum,dataStartRowNum);
    }

    public <T> List<T> importExcel(String sheetName,Class<T> clazz,int columnNameRowNum,int dataStartRowNum) throws Exception {
        Sheet sheet = getSheet(sheetName);
        return this.importExcel(sheet,clazz,columnNameRowNum,dataStartRowNum);
    }

    public <T> List<T> importExcel(Sheet sheet,Class<T> clazz,int columnNameRowNum,int dataStartRowNum) throws Exception {
        List<T> dataList = new ArrayList<>();
        Map<String,Field> nameInfos = new HashMap<>();
        Map<String,Integer> attributeInfos = new HashMap<>();
        for (Field field : this.getFiledInfo(clazz)) {
            Excel annotation = field.getAnnotation(Excel.class);
            nameInfos.put(annotation.name(),field);
        }

        Integer columnStartCellNum = 0;
        String columnName = getCellValue(sheet,columnNameRowNum,columnStartCellNum);
        while(StrUtil.isNotBlank(columnName)){
            attributeInfos.put(columnName,columnStartCellNum);
            columnName = getCellValue(sheet,columnNameRowNum,++columnStartCellNum);
        }

        String cellValue = getCellValue(sheet, dataStartRowNum, 0);
        while(StrUtil.isNotBlank(cellValue)){
            T t = clazz.newInstance();
            for (String name : attributeInfos.keySet()) {
                Integer rowNum = attributeInfos.get(name);
                Field field = nameInfos.get(name);
                cellValue = getCellValue(sheet,dataStartRowNum,rowNum);
                if(StrUtil.isNotBlank(cellValue)){
                    checkType(t,field,cellValue);
                }
            }
            cellValue = getCellValue(sheet, ++dataStartRowNum, 0);
            dataList.add(t);
        }

        return dataList;
    }

    protected <T>  List<Field> getFiledInfo(Class<T> clazz) {
        List<Field> fields = new ArrayList<>();
        Class parent = clazz;
        while (parent != Object.class){
            for (Field field : clazz.getDeclaredFields()) {
                Excel annotation = field.getAnnotation(Excel.class);
                if(null != annotation){
                    fields.add(field);
                }
            }
            parent = parent.getSuperclass();
        }
        return fields;
    }

    private static<T> void checkType(T t,Field field,String val)  throws  Exception  {
        String simpleName = field.getType().getSimpleName();
        simpleName = simpleName.toLowerCase();
        field.setAccessible(true);
        switch (simpleName){
            case JavaType.INTEGER:
                field.set(t,Integer.parseInt(val));
                break;
            case JavaType.FLOAT:
                field.set(t,Float.parseFloat(val));
                break;
            case JavaType.DOUBLE:
                field.set(t,Double.parseDouble(val));
                break;
            case JavaType.BIGDECIMAL:
                field.set(t, new BigDecimal(val));
                break;
            case JavaType.DATE:
                field.set(t,DateUtil.parse(val));
                break;
            default:
                field.set(t,val);
                break;
        }
        field.setAccessible(false);
    }



}
